package ext.vpde.cadvaulting.util;

import java.sql.*;
import wt.dataservice.DSProperties;

public class WINDSQL {
	
	public static Connection conn = null;
	public static final boolean VERBOSE = false;
	
	public static void openConnection() throws Exception {
		Class.forName(DSProperties.JDBC_DRIVER);
		String jdbcURL = DSProperties.JDBC_URL + "@" + DSProperties.SERVICE_NAME;
		conn  = DriverManager.getConnection(jdbcURL,DSProperties.DB_USER,DSProperties.DB_USER);
	}
	
	public static void closeConnection() throws Exception {
		conn.close();
	}
	
	public static boolean doSQLSave(String sqlStr) throws SQLException {
		boolean returnOK = false;
		Statement stmt = conn.createStatement();
		ResultSet rset = null;
		try {
			rset = stmt.executeQuery(sqlStr);
			conn.commit();
			returnOK = true;
		}
		catch (SQLException sqle) { 
			if (VERBOSE) System.out.println(sqle.getMessage() + " " + sqlStr);
			if (VERBOSE) System.out.println(rset.toString());
			returnOK = false;
		}
		stmt.close();
		return returnOK;
	}
	
	public static String doSQLGet(String sqlStr) throws SQLException {
		String returnVal = null;
		Statement stmt   = conn.createStatement();
		try {
			ResultSet rset = stmt.executeQuery(sqlStr);
			rset.next();
			returnVal = String.valueOf(rset.getLong(1));
		}
		catch (SQLException sqle) { 
			if (VERBOSE) System.out.println(sqle.getMessage() + " " + sqlStr);
			returnVal = null;
		}
		stmt.close();
		return returnVal;
	}
	
	public static String getNextId() throws SQLException  {
		return doSQLGet("select id_sequence.nextval from dual");
	}

	public static boolean documentExists(String cadname)  throws SQLException  {
		String cntStr = doSQLGet("select count(cadname) from epmdocumentmaster where cadname='" + cadname + "'");
		return (cntStr.equals("0")) ? false : true;
	}

	public static boolean documentExists(String cadname, String version)  throws SQLException  {
		String sqlStr  = "select count(ida2a2) from epmdocument where ";
		sqlStr        += "versionida2versioninfo='" + version + "' and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "')";
		String cntStr  = doSQLGet(sqlStr);
		return (cntStr.equals("0")) ? false : true;
	}

	public static boolean documentExists(String cadname, String version, String iteration)  throws SQLException  {
		String sqlStr  = "select count(ida2a2) from epmdocument where ";
		sqlStr        += "iterationida2iterationinfo=" + iteration + " and ";
		sqlStr        += "versionida2versioninfo='" + version + "' and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "')";
		String cntStr  = doSQLGet(sqlStr);
		return (cntStr.equals("0")) ? false : true;
	}
	
	public static String getLatestIteration(String cadname, String version) throws SQLException {
		String sqlStr  = "select ida2a2 from epmdocument where ";
		sqlStr        += "versionida2versioninfo='" + version + "' and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "') ";
		sqlStr        += "order by ida2a2 desc";
		return doSQLGet(sqlStr);
	}
	
	public static String getDomainRef(String cadname) throws SQLException {
		String sqlStr  = "select ida3domainref from epmdocument where ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "') ";
		sqlStr        += "order by ida2a2 desc";
		return doSQLGet(sqlStr);
	}
	
	public static String getLatestRevision(String cadname) throws SQLException {
		String sqlStr  = "select ida2a2 from epmdocument where latestiterationinfo=1 and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "') ";
		sqlStr        += "order by ida2a2 desc";
		return "OR:wt.epm.EPMDocument:" + doSQLGet(sqlStr);
	}	

	public static String getDocument(String cadname, String version, String iteration) throws SQLException   {
		String sqlStr  = "select ida2a2 from epmdocument where ";
		sqlStr        += "iterationida2iterationinfo=" + iteration + " and ";
		sqlStr        += "versionida2versioninfo='" + version + "' and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "')";
		return doSQLGet(sqlStr);
	}
	
	public static String getLatestDocument(String cadname, String version) throws SQLException   {
		String sqlStr  = "select ida2a2 from epmdocument where latestiterationinfo=1 and ";
		sqlStr        += "versionida2versioninfo='" + version + "' and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "')";
		return "OR:wt.epm.EPMDocument:" + doSQLGet(sqlStr);
	}

	public static String getIdOfDocumentMaster(String cadname)  throws SQLException  {
		return doSQLGet("select ida2a2 from epmdocumentmaster where cadname='" + cadname + "'");
	}

	public static String getIdFromDocumentName(String cadname, String version, String iteration) throws Exception  {
		
		String sqlStr  = "select ida2a2 from epmdocument ";
		sqlStr        += "where versionida2versioninfo='" + version + "' and ";
		sqlStr        += "iterationida2iterationinfo=" + iteration + " and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where cadname='" + cadname + "')";
		
		return doSQLGet(sqlStr);
	}
	
	public static String getIdFromDocumentNumber(String docNumber, String version, String iteration) throws Exception  {
		String sqlStr  = "select ida2a2 from epmdocument ";
		sqlStr        += "where versionida2versioninfo='" + version + "' and ";
		sqlStr        += "iterationida2iterationinfo=" + iteration + " and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmdocumentmaster where documentnumber='" + docNumber + "')";
		String ida2a2 = doSQLGet(sqlStr);
		return ida2a2;
	}

	public static String getObidFromFamilyTableName(String name) throws SQLException  {
		String sqlStr  = "select ida2a2 from epmsepfamilytable where latestiterationinfo=1 and ida3masterreference in ";
		sqlStr        += "(select ida2a2 from epmsepfamilytablemaster where name='" + name + "') ";
		sqlStr        += "order by ida2a2 desc"; 
		return "OR:wt.epm.familytable.EPMSepFamilyTable:" + doSQLGet(sqlStr);
	}
	
	public static String getIdFromFamilyTableName(String note) throws SQLException  {
		String sqlStr  = "select ida2a2 from epmsepfamilytable where noteiterationinfo='" + note + "'"; 
		return doSQLGet(sqlStr);
	}
	
	public static void removeFamilyTableLinks(String ida3b5) throws SQLException  {
		String sqlStr = "delete from epmcontainedin where ida3b5=" + ida3b5;
		doSQLSave(sqlStr);
	}

	public static boolean updateFamilyTableStatus(String ida2a2, int value) throws SQLException {
		String sqlStr = "update epmdocument set familytablestatus=" + value + " where ida2a2=" + ida2a2;
		return doSQLSave(sqlStr);
	}
	
	public static void updateHolderToContent(String genida2a2, String famida2a2) throws SQLException {
		String sqlStr   = "update holdertocontent set ida3a5=" + famida2a2 + ",";
		sqlStr         += "CLASSNAMEKEYROLEAOBJECTREF='wt.epm.familytable.EPMSepFamilyTable' where ida3a5=" + genida2a2;
		doSQLSave(sqlStr);
	}

	public static void updateName(String ida2a2, String name) throws SQLException {
		if (name.indexOf("'")>=0) name = name.replaceAll("'","''");
		String sqlStr  = "update epmdocumentmaster set name='" + name + "' where ida2a2 in ";
		sqlStr += "(select ida3masterreference from epmdocument where ida2a2=" + ida2a2 + ")";
		doSQLSave(sqlStr);
	}
	
	public static void updateNote(String ida2a2, String note) throws SQLException {
		String sqlStr  = "update epmsepfamilytable set noteiterationinfo='" + note + "' where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static void updateIteration(String ida2a2, String iteration, String domainRef) throws Exception {
		String sqlStr = "update epmdocument set ida3domainref=" + domainRef + ",iterationida2iterationinfo=" + iteration + " where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static void updateIteration(String ida2a2, String iteration) throws Exception {
		String sqlStr = "update epmdocument set iterationida2iterationinfo=" + iteration + " where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static void updateFamilyTableIteration(String ida2a2, String iteration) throws Exception {
		String sqlStr = "update epmsepfamilytable set iterationida2iterationinfo=" + iteration + " where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static void updateState(String ida2a2, String state) throws Exception {
		String sqlStr = "update epmdocument set statestate='" + state + "' where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static void updateSubType(String ida2a2, String subType) throws Exception {
		String sqlStr = "update epmdocumentmaster set docsubtype='" + subType + "' where ida2a2 in ";
		sqlStr += "(select ida3masterreference from epmdocument where ida2a2=" + ida2a2 + ")";
		doSQLSave(sqlStr);
	}
	
	public static void fixCreateStamp(String ida2a2, String creator, String created, String modifier, String modified)  throws SQLException {
		String cIda2a2 = getUserID(creator);
		String mIda2a2 = getUserID(modifier);
		String sqlStr  = "update epmdocument set ida3d2iterationinfo=" + cIda2a2 + ",";
		sqlStr += "ida3b2iterationinfo=" + mIda2a2 + ",";
		sqlStr += "createstampa2=to_date('" + created  + "','DD-MM-YYYY HH24:MI:SS'),";
		sqlStr += "modifystampa2=to_date('" + modified + "','DD-MM-YYYY HH24:MI:SS'),";
		sqlStr += "updatestampa2=to_date('" + modified + "','DD-MM-YYYY HH24:MI:SS') ";
		sqlStr += "where ida2a2=" + ida2a2;
		doSQLSave(sqlStr);
	}
	
	public static String getUserID(String user)  throws SQLException {
		return doSQLGet("select ida2a2 from wtuser where name='" + user + "'");
	}

	public static boolean createSourceIBA(String ida2a2, String sourceCenter, String sourceId)  throws SQLException {
		String sqlStr  = "insert into integervalue (IDA3A5,HIERARCHYIDA6,CLASSNAMEKEYA6,IDA3A6,CLASSNAMEKEYA4,IDA3A4,";
		sqlStr += "CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2,VALUE) ";
		sqlStr += "select 0,HIERARCHYID,CLASSNAMEA2A2,IDA2A2,'wt.epm.EPMDocument'," + ida2a2 + ",sysdate,0,";
		sqlStr += "sysdate,'wt.iba.value.IntegerValue'," + getNextId() + ",1,sysdate," + sourceId;
		sqlStr += " from integerdefinition where name='com.whirlpool.SourceId'";
		doSQLSave(sqlStr);
		sqlStr  = "insert into stringvalue (IDA3A5,HIERARCHYIDA6,CLASSNAMEKEYA6,IDA3A6,CLASSNAMEKEYA4,IDA3A4,";
		sqlStr += "CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2,VALUE,VALUE2) ";
		sqlStr += "select 0,HIERARCHYID,CLASSNAMEA2A2,IDA2A2,'wt.epm.EPMDocument'," + ida2a2 + ",sysdate,0,";
		sqlStr += "sysdate,'wt.iba.value.StringValue'," + getNextId() + ",1,sysdate,'" + sourceCenter + "','" + sourceCenter + "' ";
		sqlStr += " from stringdefinition where name='com.whirlpool.ProductCenterSource'";
		return doSQLSave(sqlStr);
	}
	
	public static boolean createVariantLink(String genMaster, String insIda2a2)  throws SQLException {
		String sqlStr  = "insert into epmvariantlink (REQUIRED,";
		sqlStr        += "CLASSNAMEKEYROLEAOBJECTREF,IDA3A5,CLASSNAMEKEYROLEBOBJECTREF,IDA3B5,";
		sqlStr        += "CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,";
		sqlStr        += "CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2) ";
		sqlStr        += "values (1,'wt.epm.EPMDocument',";
		sqlStr        += insIda2a2 + ",'wt.epm.EPMDocumentMaster',";
		sqlStr        += genMaster  + ",sysdate,0,sysdate,";
		sqlStr        += "'wt.epm.structure.EPMVariantLink'," + getNextId() + ",";
		sqlStr        += "1,sysdate)";
		return doSQLSave(sqlStr);
	}

	public static boolean createContainedInLink(String name, String docIda2a2, String familyIda2a2)  throws SQLException  {
		String sqlStr  = "insert into epmcontainedin (CONTAINEDTYPE,IDENTIFIER,LOCKED,";
		sqlStr        += "CLASSNAMEKEYROLEAOBJECTREF,IDA3A5,CLASSNAMEKEYROLEBOBJECTREF,IDA3B5,";
		sqlStr        += "CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,";
		sqlStr        += "CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2,VERIFYSTATUS) ";
		sqlStr        += "values ('FAMILYINSTANCE','" + name + "',0,'wt.epm.EPMDocument',";
		sqlStr        += docIda2a2 + ",'wt.epm.familytable.EPMSepFamilyTable',";
		sqlStr        += familyIda2a2  + ",sysdate,0,sysdate,";
		sqlStr        += "'wt.epm.structure.EPMContainedIn'," + getNextId() + ",";
		sqlStr        += "1,sysdate,1)";
		return doSQLSave(sqlStr);
	}
		
	public static boolean createReferenceLink(String parentIda2a2, String parStr, String childIda2a2, String childName, String refType)  throws SQLException  {
		String sqlStr  = "select branchiditerationinfo,ida2a2 from wttypedefinition ";
		sqlStr        += "where latestiterationinfo=1 and name='com.whirlpool.DefaultEPMReferenceLink'";
		Statement stmt = conn.createStatement();
		String branchId = null;
		String ida2a2   = null;
		try {
			ResultSet rset = stmt.executeQuery(sqlStr);
			rset.next();
			branchId = String.valueOf(rset.getLong(1));
			ida2a2   = String.valueOf(rset.getLong(2));
		} catch (Exception e) {
			if (VERBOSE) System.out.println(sqlStr);
		}
		stmt.close();

		sqlStr  = "insert into epmreferencelink (ASSTOREDCHILDNAME,DEPTYPE,";
		sqlStr        += "HASIBAVALUES,REFERENCETYPE,REQUIRED,";
		sqlStr        += "CLASSNAMEKEYROLEAOBJECTREF,IDA3A5,CLASSNAMEKEYROLEBOBJECTREF,IDA3B5,";
		sqlStr        += "CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,";
		sqlStr        += "CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2,";
		sqlStr        += "BRANCHIDA2TYPEDEFINITIONREFE,IDA2TYPEDEFINITIONREFERENCE) values (";
		sqlStr        += "'" + childName + "',4,0,'" + refType + "',1,'wt.epm.EPMDocument',";
		sqlStr        += parentIda2a2 + ",'wt.epm.EPMDocument',";
		sqlStr        += childIda2a2  + ",sysdate,0,sysdate,";
		sqlStr        += "'wt.epm.structure.EPMReferenceLink'," + getNextId() + ",";
		sqlStr        += "1,sysdate," + branchId + "," + ida2a2 + ")";
		if (doSQLSave(sqlStr)) {
			return true;
		} else {
			if (VERBOSE) System.out.println("UNABLE TO CREATE REFERENCE LINK BETWEEN " + parStr + " AND " + childName);
			return false;
		}
	}

	public static boolean createMemberLink(String parentIda2a2, String parStr, String childIda2a2, String childName) throws SQLException {
		String sqlStr  = "select branchiditerationinfo,ida2a2 from wttypedefinition ";
		sqlStr        += "where latestiterationinfo=1 and name='com.whirlpool.DefaultEPMMemberLink'";
		Statement stmt = conn.createStatement();
		String branchId = null;
		String ida2a2   = null;
		try {
			ResultSet rset = stmt.executeQuery(sqlStr);
			rset.next();
			branchId = String.valueOf(rset.getLong(1));
			ida2a2   = String.valueOf(rset.getLong(2));
		} catch(Exception e) {
			if (VERBOSE) System.out.println(sqlStr);
		}
		
		stmt.close();

		sqlStr  = "insert into epmmemberlink (ANNOTATED,ASSTOREDCHILDNAME,DEPTYPE,";
		sqlStr        += "HASIBAVALUES,PLACED,AMOUNTC7,UNITC7,REFERENCECONTROLISNULL,REQUIRED,";
		sqlStr        += "CLASSNAMEKEYROLEAOBJECTREF,IDA3A5,CLASSNAMEKEYROLEBOBJECTREF,IDA3B5,";
		sqlStr        += "SUBSTITUTE,SUPPRESSED,CREATESTAMPA2,MARKFORDELETEA2,MODIFYSTAMPA2,";
		sqlStr        += "CLASSNAMEA2A2,IDA2A2,UPDATECOUNTA2,UPDATESTAMPA2,TRANSFORMISNULL,";
		sqlStr        += "BRANCHIDA2TYPEDEFINITIONREFE,IDA2TYPEDEFINITIONREFERENCE) values (";
		sqlStr        += "0,'" + childName + "',2,0,1,1,'ea',1,1,'wt.epm.EPMDocument',";
		sqlStr        += parentIda2a2 + ",'wt.epm.EPMDocumentMaster',";
		sqlStr        += childIda2a2  + ",0,0,sysdate,0,sysdate,";
		sqlStr        += "'wt.epm.structure.EPMMemberLink'," + getNextId() + ",";
		sqlStr        += "1,sysdate,1," + branchId + "," + ida2a2 + ")";
		if (doSQLSave(sqlStr)) {
			return true;
		} else {
			if (VERBOSE) System.out.println("UNABLE TO CREATE MEMBER LINK BETWEEN " + parStr + " AND " + childName);
			return false;
		}
	}
			
	public static boolean updateLatestIteration(String ida2a2) throws SQLException {
		String sqlStr = "update epmdocument set LATESTITERATIONINFO=0 where ida2a2=" + ida2a2;
		return doSQLSave(sqlStr);
	}
}